import pymysql.cursors
import numpy as py
import pymysql
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump
import datetime
import requests

class WeatherUtils(object):
    '''
    天气类
    '''
    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01'
        ]
        self.url = 'http://v1.yiketianqi.com/api'
        
    def get_data(self):
        '''
        获取天气数据
        '''
        data_list = []
        for d in self.date_list:
            conf = {
                'appid'    : '23414543',# 使用自己注册的appid 是    string    用户appid    注册开发账号
                'appsecret': '8NBxomoL',#    是    string    用户appsecret    
                'version': 'history', #    是    string    接口版本标识    固定值: history 每个接口的version值都不一样
                'year':    d[:4],  #是    string    年份    如: 2015
                'month':d[5:7], #是    string    月份    如: 5
                'city': '南昌'    #否    string    城市名称    不要带市和区; 如: 青岛、铁西
            }
            #发起请求获取数据
            res = requests.get(self.url + '?',params=conf)
            res_data = res.json()
            for i in res_data['data']:
                data_list.append({
                    'date': datetime.datetime.strptime(i['ymd'],'%Y-%m-%d'),
                    'bWendu': i['bWendu'],
                    'yWendu': i['yWendu'],
                    'tianqi': i['tianqi'],
                    'fengli': i['fengli'],
                })
        df = pd.DataFrame(data_list)
        df.to_csv('./timing/weather.csv')

class MysqlUtls(object):
    
    def __init__(self):
        self.conn=pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )
        self.weather_data = pd.read_csv('./timing/weather.csv')
        
    def is_holiday(self,date):
        """是否节假日判断
        """
        if date in ['2024-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04','2024-10-05','2024-10-06','2024-10-07','2025-01-01','2025-01-02','2025-01-03']:
            return 1
        return 0
        
    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date,count(*) as count 
        FROM order_user_gate_rel g WHERE DATE(g.create_time) < '2025-01-01' GROUP BY date
        """
            
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
     
        #合并天气数据
        self.weather_data['date'] =pd.to_datetime(self.weather_data['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(self.weather_data,df,on='date')
        #print(df_pivot)
        df_pivot.set_index('date',inplace=True)
        
        df_pivot['dow'] = df_pivot.index.dayofweek # 星期几（0-6）
        df_pivot['month'] = df_pivot.index.month #月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        print(df_pivot.head)
        #对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot,columns=['dow','month','tianqi','fengli'], dtype=int)
        #对温度进行类型转换
        df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('°','').astype(int)
        df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('°','').astype(int)
        #print(df_pivot)
        
        #归一化入园人数
        
        
        scaler = MinMaxScaler()
        features = df[['count']]
        df['count'] = scaler.fit_transform(features)
        dump(scaler,'timing/scaler.joblib')
        #归一化温度 
        weather_features = df_pivot[['bWendu','yWendu']]
        df_pivot[['bWendu','yWendu']] = scaler.fit_transform(weather_features)
        dump(scaler,'timing/weather_scaler.joblib')
        #print(df_pivot.head)
        
        df_pivot.to_csv('timing/scenic_data.csv',index=False)
        
if __name__ == '__main__':
    mu = MysqlUtls()
    mu.get_scenic_data()
    # wu = WeatherUtils()
    # wu.get_data()